package com.returnsoft.security.eao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import com.returnsoft.security.dto.DepartmentDto;
import com.returnsoft.security.dto.PersonDto;
import com.returnsoft.security.dto.UserDto;
import com.returnsoft.security.entity.Department;
import com.returnsoft.security.entity.Person;
import com.returnsoft.security.entity.User;
import com.returnsoft.security.exception.EaoExcepcion;

@LocalBean
@Stateless
public class UserEao implements Serializable{
	private static final long serialVersionUID = 1L;
	@PersistenceContext
	private EntityManager em;

	public User findByPersonId(Integer personId) throws EaoExcepcion {
		try {
			TypedQuery<User> q = em
					.createQuery(
							"SELECT u FROM User u left join u.person p WHERE p.id = :personId",
							User.class);
			q.setParameter("personId", personId);
			User user = q.getSingleResult();

			return user;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}
	
	public User findByDocumentNumber(String documentNumber) throws EaoExcepcion {
		try {
			TypedQuery<User> q = em
					.createQuery(
							"SELECT u FROM User u join u.person p WHERE p.documentNumber = :documentNumber",
							User.class);
			q.setParameter("documentNumber", documentNumber);
			User user = q.getSingleResult();

			return user;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}
	

	public UserDto findById(Integer userId) throws EaoExcepcion {
		try {
			System.out.println("en el eao: inicia findById");
			/*TypedQuery<User> q = em.createQuery(
					"SELECT u FROM User u WHERE u.username = :username", User.class);
			q.setParameter("username", username);
			User user = q.getSingleResult();*/
			
			String query = "SELECT u.id,u.username,u.password,u.isActive,"
					+ "p.id,p.firstname,p.lastname,p.documentNumber,"
					+ "d.id,d.name,dp.id,dp.name "
					+ "FROM User u left join u.person p left join p.department d "
					+ "left join d.department dp "
					+ "WHERE u.id = :userId ";
			//System.out.println("username"+username);
			Query q = em.createQuery(query);
			q.setParameter("userId", userId);
			Object[] userObject = (Object[]) q.getSingleResult();
			

			Integer id = (Integer) userObject[0];
			String username = (String) userObject[1];
			String password = (String) userObject[2];
			Boolean isActive = (Boolean) userObject[3];
			Integer idPerson = (Integer) userObject[4];
			String firstname = (String) userObject[5];
			String lastname = (String) userObject[6];
			String documentNumber = (String) userObject[7];
			
			Integer subDepartmentId = (Integer) userObject[8];
			String subDepartmentName = (String) userObject[9];
			
			Integer departmentId = (Integer) userObject[10];
			String departmentName = (String) userObject[11];

			UserDto user = new UserDto();
			user.setId(id);
			user.setUsername(username);
			user.setPassword(password);
			user.setIsActive(isActive);
			if (idPerson!=null && idPerson>0) {
				PersonDto person = new PersonDto();
				person.setId(idPerson);
				person.setFirstname(firstname);
				person.setLastname(lastname);
				person.setDocumentNumber(documentNumber);
				if (subDepartmentId!=null && subDepartmentId>0) {
					DepartmentDto subDepartment = new DepartmentDto();
					subDepartment.setId(subDepartmentId);
					subDepartment.setName(subDepartmentName);
					if (departmentId!=null && departmentId>0) {
						DepartmentDto department = new DepartmentDto();
						department.setId(departmentId);
						department.setName(departmentName);
						subDepartment.setDepartment(department);
					}
					person.setDepartment(subDepartment);
				}
				user.setPerson(person);	
			}
			
			
			System.out.println("en el eao: termino findById");

			return user;

		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}

	}

	public UserDto findByUsername(String username) throws EaoExcepcion {
		try {
			System.out.println("en el eao: inicia findByUsername");
			/*TypedQuery<User> q = em.createQuery(
					"SELECT u FROM User u WHERE u.username = :username", User.class);
			q.setParameter("username", username);
			User user = q.getSingleResult();*/
			
			String query = "SELECT u.id,u.username,u.password,u.isActive,"
					+ "p.id,p.firstname,p.lastname,p.documentNumber,"
					+ "d.id,d.name,dp.id,dp.name "
					+ "FROM User u left join u.person p left join p.department d "
					+ "left join d.department dp "
					+ "WHERE u.username = :username ";
			//System.out.println("username"+username);
			Query q = em.createQuery(query);
			q.setParameter("username", username);
			Object[] userObject = (Object[]) q.getSingleResult();
			

			Integer id = (Integer) userObject[0];
			username = (String) userObject[1];
			String password = (String) userObject[2];
			Boolean isActive = (Boolean) userObject[3];
			Integer idPerson = (Integer) userObject[4];
			String firstname = (String) userObject[5];
			String lastname = (String) userObject[6];
			String documentNumber = (String) userObject[7];
			
			Integer subDepartmentId = (Integer) userObject[8];
			String subDepartmentName = (String) userObject[9];
			
			Integer departmentId = (Integer) userObject[10];
			String departmentName = (String) userObject[11];

			UserDto user = new UserDto();
			user.setId(id);
			user.setUsername(username);
			user.setPassword(password);
			user.setIsActive(isActive);
			if (idPerson!=null && idPerson>0) {
				PersonDto person = new PersonDto();
				person.setId(idPerson);
				person.setFirstname(firstname);
				person.setLastname(lastname);
				person.setDocumentNumber(documentNumber);
				if (subDepartmentId!=null && subDepartmentId>0) {
					DepartmentDto subDepartment = new DepartmentDto();
					subDepartment.setId(subDepartmentId);
					subDepartment.setName(subDepartmentName);
					if (departmentId!=null && departmentId>0) {
						DepartmentDto department = new DepartmentDto();
						department.setId(departmentId);
						department.setName(departmentName);
						subDepartment.setDepartment(department);
					}
					person.setDepartment(subDepartment);
				}
				user.setPerson(person);	
			}
			
			
			System.out.println("en el eao: termina findByUsername");

			return user;

		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}

	}

	public User addUser(User user) throws EaoExcepcion {
		try {

			user = em.merge(user);

			em.flush();

			return user;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	@SuppressWarnings("unchecked")
	public List<UserDto> findList(List<Integer> departmentsId,
			List<Integer> subDepartmentsId,
			String documentNumber, String username, String firstname,
			String lastname) throws EaoExcepcion {
		
		//System.out.println("Ingreso a findUSers");
		
		
		try {

			String query = "SELECT u.id,u.username,u.password,u.isActive,"
					+ "p.id,p.documentNumber,p.firstname,p.lastname,"
					+ "d.id,d.name,dp.id,dp.name " 
					+ "FROM User u "
					+ "left join u.person p " 
					+ "left join p.department d "
					+ "left join d.department dp " 
					+ "WHERE u.id>0 ";

			if (departmentsId != null && departmentsId.size() > 0) {
				query += " and d.id in :departmentsId ";
			}

			if (subDepartmentsId != null && subDepartmentsId.size() > 0) {
				query += " and dp.id in :subDepartmentsId ";
			}

			if (username != null && !username.trim().equals("")) {
				query += " and u.username = :username ";
			}

			if (documentNumber != null && !documentNumber.trim().equals("")) {
				query += " and p.documentNumber like :documentNumber ";
			}

			if (firstname != null && !firstname.trim().equals("")) {
				query += " and p.firstname like :firstname ";
			}

			if (lastname != null && !lastname.trim().equals("")) {
				query += " and p.lastname like :lastname ";
			}
			System.out.println(query);

			Query q = em.createQuery(query);

			if (departmentsId != null && departmentsId.size() > 0) {
				q.setParameter("departmentsId", departmentsId);
			}

			if (subDepartmentsId != null && subDepartmentsId.size() > 0) {
				q.setParameter("subDepartmentsId", subDepartmentsId);
			}

			if (username != null && !username.trim().equals("")) {
				q.setParameter("username", username);
			}

			if (documentNumber != null && !documentNumber.trim().equals("")) {
				q.setParameter("documentNumber", "%" + documentNumber + "%");
			}

			if (firstname != null && !firstname.trim().equals("")) {
				q.setParameter("firstname", "%" + firstname + "%");
			}

			if (lastname != null && !lastname.trim().equals("")) {
				q.setParameter("lastname", "%" + lastname + "%");
			}

			List<Object[]> userObjectList = (List<Object[]>) q.getResultList();
			
			//System.out.println("cantidad de usuarios encontrados en el eao: "+userObjectList.size());

			List<UserDto> users = new ArrayList<UserDto>();
			if (userObjectList.size() > 0) {
				for (Object[] userObject : userObjectList) {

					Integer userId = (Integer) userObject[0];
					String userUsername = (String) userObject[1];
					String userPassword = (String) userObject[2];
					Boolean userIsActive = (Boolean) userObject[3];
					Integer personId = (Integer) userObject[4];
					String personDocumentNumber = (String) userObject[5];
					String personFirstname = (String) userObject[6];
					String personLastname = (String) userObject[7];
					Integer personDepartmentId = (Integer) userObject[8];
					String personDepartmentName = (String) userObject[9];
					Integer personDepartmentParentId = (Integer) userObject[10];
					String personDepartmentParentName = (String) userObject[11];

					UserDto user = new UserDto();
					user.setId(userId);
					user.setUsername(userUsername);
					user.setPassword(userPassword);
					user.setIsActive(userIsActive);
					if (personId != null && personId > 0) {
						PersonDto person = new PersonDto();
						person.setId(personId);
						person.setDocumentNumber(personDocumentNumber);
						person.setFirstname(personFirstname);
						person.setLastname(personLastname);
						if (personDepartmentId != null) {
							DepartmentDto department = new DepartmentDto();
							department.setId(personDepartmentId);
							department.setName(personDepartmentName);
							if (personDepartmentParentId != null) {
								DepartmentDto departmentParent = new DepartmentDto();
								departmentParent
										.setId(personDepartmentParentId);
								departmentParent
										.setName(personDepartmentParentName);
								department.setDepartment(departmentParent);
							}
							person.setDepartment(department);
						}
						user.setPerson(person);
					}
					users.add(user);

				}
			}
			return users;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}
	
	
	
	@SuppressWarnings("unchecked")
	public List<User> findUsersByPermission(String documentNumber, String firstname,
			String lastname, String permissionCode) throws EaoExcepcion {
		
		//System.out.println("Ingreso a findUSers");
		
		
		try {

			String query = "SELECT u.id,"
					+ "p.id,p.documentNumber,p.firstname,p.lastname "
					+ "FROM User u "
					+ "left join u.person p "
					+ "left join u.permissions ps "
					+ "WHERE u.id>0 and ps.code like :permissionCode ";
			

			if (documentNumber != null && !documentNumber.trim().equals("")) {
				query += " and p.documentNumber like :documentNumber ";
			}

			if (firstname != null && !firstname.trim().equals("")) {
				query += " and p.firstname like :firstname ";
			}

			if (lastname != null && !lastname.trim().equals("")) {
				query += " and p.lastname like :lastname ";
			}


			Query q = em.createQuery(query);
			
			q.setParameter("permissionCode", permissionCode+"%");

			if (documentNumber != null && !documentNumber.trim().equals("")) {
				q.setParameter("documentNumber", "%" + documentNumber + "%");
			}

			if (firstname != null && !firstname.trim().equals("")) {
				q.setParameter("firstname", "%" + firstname + "%");
			}

			if (lastname != null && !lastname.trim().equals("")) {
				q.setParameter("lastname", "%" + lastname + "%");
			}

			List<Object[]> userObjectList = (List<Object[]>) q.getResultList();
			
			//System.out.println("cantidad de usuarios encontrados en el eao: "+userObjectList.size());

			List<User> users = new ArrayList<User>();
			if (userObjectList.size() > 0) {
				for (Object[] userObject : userObjectList) {

					Integer userId = (Integer) userObject[0];
					Integer personId = (Integer) userObject[1];
					String personDocumentNumber = (String) userObject[2];
					String personFirstname = (String) userObject[3];
					String personLastname = (String) userObject[4];

					User user = new User();
					user.setId(userId);
					if (personId != null && personId > 0) {
						Person person = new Person();
						person.setId(personId);
						person.setDocumentNumber(personDocumentNumber);
						person.setFirstname(personFirstname);
						person.setLastname(personLastname);
						user.setPerson(person);
					}
					users.add(user);
				}
			}
			return users;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<User> findUsers(List<Integer> departmentsId,
			List<Integer> subDepartmentsId, String documentNumber, String firstname,
			String lastname, String username, String permissionCode) throws EaoExcepcion {
		
		//System.out.println("Ingreso a findUSers");
		
		
		try {

			String query = "SELECT u.id,u.username,"
					+ "p.id,p.documentNumber,p.firstname,p.lastname,"
					+ "d.id,d.name,dp.id,dp.name " 
					+ "FROM User u "
					+ "left join u.person p "
					+ "left join u.permissions ps "
					+ "left join p.department d "
					+ "left join d.department dp " 
					+ "WHERE u.id>0 and ps.code like :permissionCode ";
			
			if (departmentsId != null && departmentsId.size() > 0) {
				query += " and d.id in :departmentsId ";
			}

			if (subDepartmentsId != null && subDepartmentsId.size() > 0) {
				query += " and dp.id in :subDepartmentsId ";
			}

			if (documentNumber != null && !documentNumber.trim().equals("")) {
				query += " and p.documentNumber like :documentNumber ";
			}

			if (firstname != null && !firstname.trim().equals("")) {
				query += " and p.firstname like :firstname ";
			}

			if (lastname != null && !lastname.trim().equals("")) {
				query += " and p.lastname like :lastname ";
			}
			
			if (username != null && !username.trim().equals("")) {
				query += " and u.username like :username ";
			}


			Query q = em.createQuery(query);
			
			q.setParameter("permissionCode", permissionCode+"%");
			
			if (departmentsId != null && departmentsId.size() > 0) {
				q.setParameter("departmentsId", departmentsId);
			}

			if (subDepartmentsId != null && subDepartmentsId.size() > 0) {
				q.setParameter("subDepartmentsId", subDepartmentsId);
			}
			
			if (documentNumber != null && !documentNumber.trim().equals("")) {
				q.setParameter("documentNumber", "%" + documentNumber + "%");
			}

			if (firstname != null && !firstname.trim().equals("")) {
				q.setParameter("firstname", "%" + firstname + "%");
			}

			if (lastname != null && !lastname.trim().equals("")) {
				q.setParameter("lastname", "%" + lastname + "%");
			}
			
			if (username != null && !username.trim().equals("")) {
				q.setParameter("username", "%" + username + "%");
			}

			List<Object[]> userObjectList = (List<Object[]>) q.getResultList();
			
			//System.out.println("cantidad de usuarios encontrados en el eao: "+userObjectList.size());

			List<User> users = new ArrayList<User>();
			if (userObjectList.size() > 0) {
				for (Object[] userObject : userObjectList) {

					Integer userId = (Integer) userObject[0];
					String userUsername = (String) userObject[1];
					Integer personId = (Integer) userObject[2];
					String personDocumentNumber = (String) userObject[3];
					String personFirstname = (String) userObject[4];
					String personLastname = (String) userObject[5];
					
					Integer personDepartmentId = (Integer) userObject[6];
					String personDepartmentName = (String) userObject[7];
					Integer personDepartmentParentId = (Integer) userObject[8];
					String personDepartmentParentName = (String) userObject[9];

					User user = new User();
					user.setId(userId);
					user.setUsername(userUsername);
					if (personId != null && personId > 0) {
						Person person = new Person();
						person.setId(personId);
						person.setDocumentNumber(personDocumentNumber);
						person.setFirstname(personFirstname);
						person.setLastname(personLastname);
						if (personDepartmentId != null) {
							Department department = new Department();
							department.setId(personDepartmentId);
							department.setName(personDepartmentName);
							if (personDepartmentParentId != null) {
								Department departmentParent = new Department();
								departmentParent
										.setId(personDepartmentParentId);
								departmentParent
										.setName(personDepartmentParentName);
								department.setDepartment(departmentParent);
							}
							person.setDepartment(department);
						}
						user.setPerson(person);
					}
					users.add(user);
				}
			}
			return users;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

}
